The series so far:

1. Introduction to HIPAA and SOX — Part 1
2. HIPAA and Database Administration — Part 2
3. SOX and Database Administration — Part 3
4. SQL Server Auditing for HIPAA and SOX — Part 4

Your organization might be storing data in a SQL Server database that’s subject to regulations such as the Health Insurance Portability and Accountability Act of 1996 (HIPAA) or the Sarbanes-Oxley Act of 2002 (SOX). If you’re a DBA managing such a database, one of the most important steps you can take is to implement an auditing strategy that monitors user activity for behavior that could lead to noncompliance.

Auditing provides a record of activities that can be used for performing forensic analysis in order to determine whether an incident has occurred and, if so, its full impact. Not only can this help to discover malicious activities but also to curb unintentional behavior that might put an organization at risk. For example, auditing can show which user ran an UPDATE statement against sensitive data in a specific table as well as who modified database objects or altered the permissions granted to a login.

Because of the important role that auditing plays in protecting data, Microsoft began incorporating auditing capabilities within the database engine starting in SQL Server 2008, with the introduction of SQL Server Audit. Since then, Microsoft has made several improvements to the service, although the core functionality remains the same.

SQL Server Audit is free in all SQL Server editions. When first introduced in SQL Server 2008, the Standard edition did not support some of the more granular auditing capabilities. Since SQL Server 2016 SP1, however, all editions include all SQL Server Audit features, making it a valuable tool for any sized organization.

Implementing SQL Server Audit

You can use SQL Server Audit to monitor user activity at the instance (server) level, at the database level, or both. To monitor activity, SQL Server Audit uses Extended Events, a configurable event framework built into the database engine. Extended Events is considered a replacement for SQL Trace because of its lower performance overhead.

SQL Server Audit is made up of several components. The top-level component is the audit object, a container for organizing the server and database audit settings and for delivering the final audit logs. You create an audit object at the instance level before configuring any other audit components. Each instance can support multiple audits.

When defining an audit, you must specify a destination, or target, for the audited data. The data can be saved to files on the local machine or to a network share. The files are automatically generated with the .sqlaudit extension.

You can also save audit data to the Windows Application event log or Windows Security event log. The Security log provides greater protection, but you must ensure that the appropriate permissions have been granted before you can save data to this log.

For each audit object, you can add a single server audit specification, which determines what type of events will be audited at the server level. Like the audit object, the specification is created at the instance level.

When you configure a server audit specification, you will need to add one or more action groups. An action group is a collection of actions that determine what events are monitored. An action is essentially a single event. For example, the action group DATABASE_CHANGE_GROUP raises an event when a database is created, altered, or dropped. The event is the execution of the CREATE, ALTER, or DROP statement. The action groups configured on a server audit specification monitor events across the entire SQL Server instance.

An audit object can also contain one or more database audit specifications, which are created at the database level. Each audit can include only one database audit specification per database. However, you can add either action groups or individual actions to a database audit specification.

The events raised by the action groups or actions in a database audit specification are specific to the database where the specification is defined. For example, you can add the DATABASE_OBJECT_CHANGE_GROUP action group in order to raise an event when a CREATE, ALTER, or DROP statement is executed against the database. However, you can also add individual actions. For instance, you can add a SELECT action to raise an event if a SELECT statement is executed or add an UPDATE action to raise an event if an UPDATE statement is executed.

You can use T-SQL to define the SQL Server Audit components, or you can use the SQL Server Management Studio (SSMS) interface. Once created, the components appear in Object Explorer, either at the database level or server level. For example, Figure 1 shows Object Explorer with the Security node expanded for both the WideWorldImporters database and for the server instance. The database includes one database audit specification, and the instance includes one server audit specification and two audit objects. The test_db_spec database audit specification and the test_audit2 audit object are both enabled. The other components are disabled, as indicated by the red X.

Figure 1. SQL Server Audit components in Object Explorer

You can define audit objects on an instance based on your specific requirements and management preferences. For example, you can set up an audit that contains only a server audit specification and then set up a second audit that contains a database audit specification for each user database that stores sensitive data. In this way, you can enable or disable server-level or database-level audits independently of each other.

Setting up an audit in SQL Server is a fairly straightforward process. You define an audit object, configure the necessary audit specifications, and then enable each component. Feodor Georgiev provides an excellent overview of this process in his Simple Talk article SQL Server Security Audit Basics.

What to Audit in SQL Server

What is perhaps the biggest challenge in setting up SQL Server Audit is to determine exactly what data to audit to ensure compliance with HIPAA, SOX, or both. It might be tempting to simply audit everything, but this approach adds overhead, produces a vast amount of unnecessary data, and makes analyzing that data increasingly difficult. The more precise you can be when setting up auditing, the more effective the overall process.

Before you implement auditing, you need to understand what you are legally obligated to audit. Your organization might have additional requirements that go beyond the regulations, but you should still know what is required. In addition, you should capture a snapshot of your instance’s security context before you start auditing so you understand who has what permissions in the event that something gets change that should not have been changed. Again, refer to Feodor Georgiev’s article.

Be aware, however, that auditing can produce massive amounts of data, so you must be prepared to handle all that information. This means, in part, that you’ll likely need to set up a system for archiving the data. More importantly, you must ensure that the audit data is secure throughout every phase of the audit and review processes, using encryption, permissions, VPNs, and other mechanisms as appropriate. Another important step is to audit the auditing process itself. For example, you can use the AUDIT_CHANGE_GROUP server-level action group to raise an alert whenever an audit object is created, modified, or deleted.

When determining which actions to audit, you should carefully review the HIPAA and SOX regulations to determine which ones are applicable to your organization. For example, Section 164.312 of the HIPAA Security Rule states that the covered entity must protect electronic protected health information from “improper alteration or destruction.” And Section 401 of the SOX regulations states that all financial information included in the SEC reports or in “any public disclosure or press or other release” shall not contain untrue statements or omit the facts necessary to understanding the corporation’s financial condition.

From both of these sections, you can deduce that data must be protected against wrongful deletions or modifications, whether done intentionally or accidently. As a result, when configuring your audit, you’ll need to add actions or action groups that help you track data modifications and deletions to ensure no questionable actions have taken place.

To this end, you’ll likely include database-level audit actions such as INSERT, UPDATE and DELETE when setting up database audit specifications. But these actions alone might not be enough to ensure that all the necessary objects are being monitored. As a result, you might also want to add such components as the action group DATABASE_OBJECT_CHANGE_GROUP, which raises alerts when a CREATE, ALTER, or DROP statement is executed against any database object.

You can refer to the previous articles in this series for more specifics about the HIPAA and SOX regulations that could apply to SQL Server data, but keep in mind that these articles are no substitute for a thorough review of the regulations themselves when it comes to ensuring that you’re addressing all potential issues. You should also consider bringing in outside expertise if you’re uncertain how to comply with any of the regulations and what actions or action groups to monitor.

How to View the Audit Data

The way in which you access the audit data depends on your target. If you saved the data to files, you can use the Log File Viewer in SSMS to view the data, as shown in Figure 2. To access the Log File Viewer, right-click the audit in Object Explorer and then click View Audit Logs.

Figure 2. Viewing audit data in the Log File Viewer

In this case, the first item listed in the Log File Viewer is based on an alert generated when running the following query against the WideWorldImporters database:

The event is listed in the log file here because a database audit specification had been set up with the SELECT action included.

You can also view the audit data by using the sys.fn_get_audit_file table-valued function. For example, the following SELECT statement uses the function to access the audit logs for the test_audit audit object:

Notice that the WHERE clause limits the results to the alerts generated on the Sales schema in the WideWorldImporters database, returning the events shown in Figure 3. In this case, the query returns only nine rows, one for each raised event, but in a production environment, you might see hundreds or thousands of rows, if not more.

Figure 3. Using the sys.fn_get_audit_file function to view audit data

Microsoft generally recommends that you use the Log File Viewer over the sys.fn_get_audit_file function because the viewer provides the data in a more user-friendly format. The viewer also includes filtering capabilities to better refine the information. That said, a little creative T-SQL will also get you the data you need when using the function. Plus, this approach is useful if you want to read the logs outside of SSMS, such as through an automated monitoring solution.

If you instead save the audit data to the Application or Security logs, you can use the Windows Event Viewer to access the data, as shown in Figure 4. Event Viewer lets you filter the data and view details about each event. You can also view the data as XML or export it to a file, using one of several of the available formats.

Figure 4. Viewing audit data in Windows Event Viewer

When audit data is saved to a Windows log, you can also use a tool such as PowerShell to retrieve the data directly. In this way, you can write scripts that pull the data you need from the logs when you need them, just like you can when using the sys.fn_get_audit_file function.

Regardless of how you save or view the audit data, you’ll find that the information is essentially the same. For example, an event might include the event time, database, schema, table, and T-SQL statement, if applicable. Each event also includes a category, or column, named action_id, which indicates the action that occurred to trigger the event. In figure 4, for example, the action_id value is SL, which is the abbreviation used to indicate that the action was a SELECT statement.

When you view the audit data through the Log File Viewer in SSMS, the action_id value is listed as the action’s full name. For example, if the action that triggered the event was a SELECT statement, the action_id value is SELECT. However, when you use any of the other tools to view the event data, you get only the abbreviations, which is why the value is listed as SL in Figure 4.

If you’re unsure what an abbreviation means, you can use the sys.dm_audit_actions dynamic management view to retrieve a list of the abbreviations and their meanings.

No matter how the audit data is saved or accessed, the bigger challenge lies in the fact that there can be massive amounts of information to monitor. Unfortunately, SQL Server does not include any useful solutions for working with the data once it’s been collected, other than to review it manually. As a result, you’ll need to set up your own system for monitoring the data or turn to a third-party solution such as EventTracker, which provides a tool for consolidating, managing and monitoring SQL Server Audit data (either through the Application log or Security log). Other options include the Splunk Add-on for SQL Server or LOGbinder for SQL Server.

Making the Most of SQL Server

For DBAs managing sensitive data stored in SQL Server databases, SQL Server Audit provides a powerful option for helping to ensure that their organizations remain in compliance with the HIPAA or SOX regulations.

In most cases, however, auditing will not be enough. For example, audit data might show who ran an UPDATE statement against a table, but it won’t tell you what the data was before it was modified. For that, you need to turn to such tools as triggers, Change Data Capture, or temporal tables. Temporal tables are a feature introduced in SQL Server 2016 that make it possible to track a table’s history of data changes.

No matter what tools you use, auditing will remain a pivotal component in any SQL Server compliance strategy. Fortunately, implementing auditing is a relatively straightforward process. The bigger challenges come when trying to decide what actions to audit and how to handle all the collected data. The better you understand the HIPAA and SOX regulations and the more thoroughly you plan your auditing strategy, the more effective your results and the better your chances of remaining in compliance.